/*******************************************************************************
 * Copyright (c) 2011 Technische Universität Darmstadt
 * 					  Fachbereich Informatik
 * 					  Theoretische Informatik - Kryptographie und Computeralgebra
 * 
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 * 
 * The above copyright notice and this permission notice shall be included in
 * all copies or substantial portions of the Software.
 * 
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 * THE SOFTWARE.
 ******************************************************************************/

package de.tud.cdc;

import java.io.OutputStream;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.io.Writer;
import java.security.InvalidKeyException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import java.util.logging.Logger;

/**
 * An implementation of the {@link Database} interface. This class provides an
 * interface to an encrypted derby database. Once the password is set it can not
 * be changed at the moment. The connection is only available if there is
 * nothing else which has a connection to the database and if the correct
 * password is provided.
 * 
 * @author Franziskus Kiefer
 * 
 */
public class Derby implements Database {

	/**
	 * The path to the database folder as {@link String}.
	 */
	private String db;
	/**
	 * The {@link Connection} to the database, stored in {@link #db}.
	 */
	private Connection conn;
	/**
	 * {@link String} constant for the SQL table name.
	 */
	private final static String TABLE_NAME = "mails";
	/**
	 * {@link String} constant for the identifier column in the SQL database.
	 */
	private final static String ID_COL = "identifier";
	/**
	 * {@link String} constant for the mail body column in the SQL database.
	 */
	private final static String BODY_COL = "body";
	/**
	 * {@link String} constant for the mail subject column in the SQL database.
	 */
	private final static String SUBJECT_COL = "subject";
	/**
	 * {@link String} constant which has to be either "true" or "false". If it
	 * is true a new database will be created if the chosen one does not exist.
	 * Otherwise an error occurs.
	 */
	private final static String CREATE_NEW_DB = "true";
	/**
	 * Public static final {@link OutputStream} that points nowhere.
	 * To get rid of the derby logging file.
	 */
	public static final OutputStream DEV_NULL = new OutputStream() {         
	    public void write(int b) { }     
	};
	/**
	 *  logger
	 */
	private final Logger logger;
	/**
	 * The db password. Need to store this to close the db.
	 */
	private String pwd;
	
	/**
	 * Default constructor for {@link Derby}. Does nothing special but
	 * {@link #init(String, String)} has to be called before this database can
	 * be used!
	 */
	public Derby(Logger newlogger) {
		System.setProperty("derby.stream.error.field", "de.tud.cdc.Derby.DEV_NULL");
		logger = newlogger;
	}

	/**
	 * Constructor for {@link Derby} database which also starts the
	 * initialization of it calling {@link #init(String, String)} with the given
	 * database path and password.
	 * 
	 * @param db
	 *            the absolute path to the database as {@link String}.
	 * @param pwd
	 *            the password to open the database as {@link String}.
	 * @throws SQLException
	 *             throws a {@link SQLException} if something went's wrong
	 *             during the initialization process (e.g. wrong password, or
	 *             database is already in use).
	 * @throws InvalidKeyException 
	 */
	protected Derby(String db, String pwd) throws SQLException, InvalidKeyException {
		this(Logger.getLogger("CBLog"));
		init(db, pwd);
	}

	/**
	 * ATTENTION: password has to be longer than 8 characters!
	 * @throws InvalidKeyException 
	 */
	@Override
	public void init(String db, String pwd) throws SQLException, InvalidKeyException {
		logger.finest("Java path: "+System.getProperty("sun.boot.library.path"));
		logger.finest("DB path: "+db);
		//logger.debug("PW : "+pwd);
		this.db = db;
		this.pwd = pwd;
		try {
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
			System.setProperty("derby.system.home", this.db);
			try{
			conn = DriverManager.getConnection("jdbc:derby:" + this.db + ";dataEncryption=true;bootPassword=" + pwd + ";encryptionAlgorithm=AES/CBC/NoPadding;encryptionKeyLength=256;create=" + CREATE_NEW_DB);
			}catch(SQLException e){
				final Writer error = new StringWriter();
			    final PrintWriter printWriter = new PrintWriter(error);
			    e.printStackTrace(printWriter);
			    if (error.toString().contains("Illegal key size")) throw new InvalidKeyException("no unlimited strength policy");
			    else throw e;
			}
			conn.setAutoCommit(false);
			Statement stat = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
			try {
				stat.executeUpdate("CREATE TABLE " + TABLE_NAME + " (" + ID_COL + " VARCHAR(256) UNIQUE NOT NULL, " + BODY_COL + " CLOB, "+SUBJECT_COL+" CLOB)");
			} catch (SQLException e) {
				logger.info("DB already created...");
			}
			stat.close();
			conn.commit();
		} catch (ClassNotFoundException e) {
			logger.severe("Error on creating database. "+ e.getLocalizedMessage());
		} 
	}

	@Override
	public void addMail(String id, String body, String subject) {
		PreparedStatement prep;
		if(subject == null){
			logger.finest("Subject empty!");
			subject="";
		}
		try {
			Statement stat = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			ResultSet rs = stat.executeQuery("SELECT " + ID_COL + " FROM " + TABLE_NAME + " WHERE " + ID_COL + "='" + id + "'");
			// If the value was not in there, put it in
			if (!rs.next()) {
				prep = conn.prepareStatement("INSERT INTO " + TABLE_NAME + " VALUES (?, ?, ?)");
				prep.setString(1, id);
				prep.setString(2, body);
				prep.setString(3, subject);
				prep.addBatch();
				prep.executeBatch();
				prep.close();
				conn.commit();
			}
			stat.close();
		} catch (SQLException e) {
			logger.warning("Error on saving email to database."+ e.getLocalizedMessage());
		}
	}

	@Override
	public String[] getMailIdWhere(String toSearch, boolean body, boolean subject) {
		Statement stat;
		Vector<String> result = new Vector<String>();
		if (!body && !subject) return new String[]{};
		try {
			stat = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			ResultSet rs = stat.executeQuery("SELECT identifier FROM " + TABLE_NAME + " WHERE " +
					(body?"LOWER(" + BODY_COL + ") LIKE '%" + toSearch.toLowerCase() + "%'":"")+
					(body&&subject?" OR ":"")+
					(subject?"LOWER(" + SUBJECT_COL + ") LIKE '%" + toSearch.toLowerCase() + "%'":"")); // is
																																								// insensitive
			while (rs.next()) {
				String s = rs.getString(ID_COL);
				logger.finest(s);
				result.add(s);
			}
			rs.close();
		} catch (SQLException e) {
			logger.warning("Error on receiving email id from database. "+ e.getLocalizedMessage());
		}

		String[] arr = (String[]) result.toArray(new String[result.size()]);
		return arr;
	}

	@Override
	public String[] getMailIdWhereFolder(String toSearch, String folder, boolean body, boolean subject) {
		Statement stat;
		Vector<String> result = new Vector<String>();
		if (!body && !subject) return new String[]{};
		try {
			stat = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			String query = "SELECT identifier FROM " + TABLE_NAME + " WHERE " +
			(body?"LOWER(" + BODY_COL + ") LIKE '%" + toSearch.toLowerCase() + "%'":"")+
			(body&&subject?" OR ":"")+
			(subject?"LOWER(" + SUBJECT_COL + ") LIKE '%" + toSearch.toLowerCase() + "%'":"") +
			" AND " + ID_COL + " LIKE '%" + folder + "#%'";
			ResultSet rs = stat.executeQuery(query); // is case insensitive
			while (rs.next()) {
				String s = rs.getString(ID_COL);
				logger.finest(s);
				if(s.toLowerCase().trim().contains(folder.toLowerCase().trim())){
					result.add(s);
				}
			}
			rs.close();
		} catch (SQLException e) {
			logger.warning("Error on receiving email id from database. "+ e.getLocalizedMessage());
		}

		String[] arr = (String[]) result.toArray(new String[result.size()]);
		return arr;
	}
	
	public String getBodyWhereMailId(String URI){
		Statement stat;
		String result = null;
		try {
			//conn.setAutoCommit(false);
			stat = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			String query = "SELECT "+BODY_COL+" FROM " + TABLE_NAME + " WHERE " + ID_COL+ "='"+URI+"'";
			logger.warning(query);
			ResultSet rs = stat.executeQuery(query); // is case insensitive
			if (rs.next()){
				result = rs.getString(1);
			} else logger.finest("URI not in DB");
			rs.close();
		} catch (SQLException e) {
			logger.warning("Error on receiving mail body by ID from database. "+ e.getLocalizedMessage());
		}
		return result;
		
	}
	
	@Override
	public String getSubjectWhereMailId(String URI) {
		Statement stat;
		String result = null;
		try {
			stat = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			String query = "SELECT "+SUBJECT_COL+" FROM " + TABLE_NAME + " WHERE " + ID_COL+ "='"+URI+"'";
			ResultSet rs = stat.executeQuery(query); // is case insensitive
		   if (!rs.next()){
				logger.finest(query + " next() returns false");
				return "";
			}
			result = rs.getString(1);
			rs.close();
		} catch (SQLException e) {
			logger.warning("Error on receiving mail subject by ID from database. "+ e.getLocalizedMessage());
		}
		return result;
	}

	@Override
	public void viewDB() {
		Statement stat;
		try {
			stat = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			ResultSet rs = stat.executeQuery("SELECT * FROM " + TABLE_NAME);
			ResultSetMetaData rsmd = rs.getMetaData();
			int numColumns = rsmd.getColumnCount(); // Get the column
													// names;column indices
													// start from 1
			Vector<String> colNames = new Vector<String>();
			for (int i = 1; i < numColumns + 1; i++) {
				colNames.add(rsmd.getColumnName(i)); // Get the name of the
														// column's table name
														// String
			}
			while (rs.next()) {
				logger.info("---------------------" + rs.getString(1) + "---------------------");
				for (String col : colNames) {
					if (col.equalsIgnoreCase("BODY"))
						logger.info(col + "\t\t|\t" + rs.getString(col));
					else
						logger.info(col + "\t|\t" + rs.getString(col));
				}
				logger.info("");
			}
			rs.close();
		} catch (SQLException e) {
			logger.warning("Error on receiving database content. "+ e.getLocalizedMessage());
		}
	}

	@Override
	protected void finalize() throws Throwable {
		try {
			conn.close();
			DriverManager.getConnection("jdbc:derby:;shutdown=true");
		} catch (SQLException e) {
			logger.warning("DB is shut down...");
		}
		super.finalize();
	}

	@Override
	public boolean checkID(String id) {
		ResultSet rs;
		try {
			rs = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY).executeQuery("SELECT " + ID_COL + " FROM " + TABLE_NAME + " WHERE " + ID_COL + "='" + id + "'");
			return rs.next();
		} catch (SQLException e) {
			logger.warning("Error on receiving email id from database. "+ e.getLocalizedMessage());
		} finally {
			rs = null;
		}
		return false;
	}

	@Override
	public void deleteEntry(String id) {
		// DELETE FROM mails WHERE identifier <
		// 'mailbox-message://cryptobird@pop.googlemail.com/Inbox#2319597';
		try {
			conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE).executeUpdate("DELETE FROM " + TABLE_NAME + " WHERE " + ID_COL + "='" + id + "'");
			conn.commit();
		} catch (SQLException e) {
			logger.warning("Error on deleting email from database. "+ e.getLocalizedMessage());
		}
	}

	@Override
	public void deleteEntries(String id) {
		// DELETE FROM mails WHERE identifier <
		// 'mailbox-message://cryptobird@pop.googlemail.com/Inbox#2319597';
		try {
			conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE).executeUpdate("DELETE FROM " + TABLE_NAME + " WHERE " + ID_COL + " LIKE '%" + id + "%'");
			conn.commit();
		} catch (SQLException e) {
			logger.warning("Error on deleting emails from database. "+ e.getLocalizedMessage());
		}
	}

	@Override
	public void changeEntry(String oldId, String newId) {
		// UPDATE mails SET
		// identifier='imap-message://alice@imap.cdc.informatik.tu-darmstadt.de/Sent#7777'
		// WHERE
		// identifier='imap-message://alice@imap.cdc.informatik.tu-darmstadt.de/Sent#7'
		try {
			conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE).executeUpdate("UPDATE " + TABLE_NAME + " SET " + ID_COL + "='" + newId + "' WHERE " + ID_COL + "='" + oldId + "'");
			conn.commit();
		} catch (SQLException e) {
			logger.warning("Error on changing email id in database. "+ e.getLocalizedMessage());
		}
	}

	@Override
	public void addCopiedEntry(String oldId, String newId) {
		// insert into mails (identifier,body) select 'neuer identifier', body
		// from mails where
		// identifier='mailbox-message://cryptobird@pop.googlemail.com/Sent#0';
		try {
			conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE).executeUpdate("INSERT into " + TABLE_NAME + " (" + ID_COL + ", " + BODY_COL + ", " + SUBJECT_COL + ") SELECT '" + newId + "', " + BODY_COL + ", " + SUBJECT_COL + " FROM " + TABLE_NAME + " WHERE " + ID_COL + "='" + oldId + "'");
			conn.commit();
		} catch (SQLException e) {
			logger.warning("Error on changing email id in database. "+ e.getLocalizedMessage());
		}
	}

	@Override
	public void changeEntryLike(String oldIdPart, String newIdPart) {
		/**
		 * var re = new RegExp('.?[^/]+$', "g");
		 */
		// cut last part to get current folder name
		String oldPath = oldIdPart.replaceAll(".?[^/]+$", "");
		String queryString = "UPDATE " + TABLE_NAME + " SET " + ID_COL + "='" + newIdPart + "'||SUBSTR(identifier, LENGTH('" + oldPath + "')+1) WHERE " + ID_COL + " LIKE '%" + oldIdPart + "%'";
		try {
			conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE).executeUpdate(queryString);
			conn.commit();
		} catch (SQLException e) {
			logger.warning("Error on changing emails from database. "+ e.getLocalizedMessage());
		}
	}

	@Override
	public void copyEntryLike(String oldIdPart, String newIdPart) {

		/**
		 * var re = new RegExp('.?[^/]+$', "g");
		 */
		// cut last part to get current folder name
		String oldPath = oldIdPart.replaceAll(".?[^/]+$", "");
		String queryString = "INSERT into " + TABLE_NAME + " (" + ID_COL + ", " + BODY_COL + ") SELECT '" + newIdPart + "'||SUBSTR(identifier, LENGTH('" + oldPath + "')+1), " + BODY_COL + " FROM " + TABLE_NAME + " WHERE " + ID_COL + " LIKE '%" + oldIdPart + "%'";
		
		try {
			conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE).executeUpdate(queryString);
			conn.commit();
		} catch (SQLException e) {
			logger.warning("Error on copying emails in database. "+ e.getLocalizedMessage());
		}
	}

	@Override
	public void clear() {
		try {
			conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE).executeUpdate("DELETE FROM " + TABLE_NAME);
			conn.commit();
		} catch (SQLException e) {
			logger.warning("Error on dropping table in database. "+ e.getLocalizedMessage());
		}
	}

	@Override
	public void shutDown() {
		try {
			conn.close();
			DriverManager.getConnection("jdbc:derby:" + this.db + ";dataEncryption=true;bootPassword=" + pwd + ";encryptionAlgorithm=AES/CBC/NoPadding;encryptionKeyLength=256;"+"shutdown=true");
		} catch (SQLException e) {
			logger.info("DB has been shut down.");
		}
	}

	@Override
	public String getDbPath() {
		return db;
	}

}
